# -*- coding: UTF-8 -*-

import re
import datetime
import sys
from sqlalchemy import and_,  func, or_
from typing import Dict, List, Any, Generator

from sqlalchemy import Boolean, Column, DateTime, Integer, String, Index, Table, ForeignKey, text, JSON, TEXT, FLOAT
from sqlalchemy.orm import Session, relationship
from common.database import Base
from common.database import SessionMixin
from common.utils import is_empty
from core.logger import logger

__all__ = ['QosHostDate']

class QosHostDate(Base, SessionMixin):
    '''
    服务器的质量信息
    '''
    __tablename__ = "qos_host_date"
    quality_date = Column(String(64), nullable=False, index=True, comment='数据日期')
    name = Column(String(256), nullable=False, index=True, comment='主机名称')
    ip = Column(String(24), nullable=False, index=True, comment='ip地址')
    # ECS服务器 or k8s集群node节点
    classes = Column(String(128), nullable=False, index=True, comment='机器归类')
    department = Column(String(128), nullable=False, index=True, comment='所属部门')
    cpu_spec = Column(Integer, nullable=False, default=0, comment='cpu规格')
    mem_spec = Column(Integer, nullable=False, default=0, comment='mem规格')
    livability = Column(FLOAT, nullable=False, default=0, comment='服务器存活率')
    # todo 磁盘使用率：系统盘使用率，数据盘（所有盘的使用字节）
    cpu_level = Column(String(64), nullable=False, index=True, default="低(cpu平均使用率<5%)", comment='cpu资源使用等级')
    cpu_usage_max = Column(FLOAT, nullable=False, default=0, comment='cpu使用率最大值')
    cpu_usage_min = Column(FLOAT, nullable=False, default=0, comment='cpu使用率最小值')
    cpu_usage_avg = Column(FLOAT, nullable=False, default=0, comment='cpu使用率平均值')
    cpu_prom_data = Column(JSON, nullable=False, default=[], comment='cpu监控数据')
    mem_level = Column(String(64), nullable=False, index=True, default="低(mem平均使用率<10%)", comment='内存资源使用等级')
    mem_usage_max = Column(FLOAT, nullable=False, default=0, comment='内存使用率最大值')
    mem_usage_min = Column(FLOAT, nullable=False, default=0, comment='内存使用率最小值')
    mem_usage_avg = Column(FLOAT, nullable=False, default=0, comment='内存使用率平均值')
    mem_prom_data = Column(JSON, nullable=False, default=[], comment='内存监控数据')
    comment = Column(String(256), nullable=True, comment='数据说明')



    __table_args__ = (
        Index('idx_qos_host_date_quality_date_name_ip', 'quality_date', 'name', "ip", unique=True),
        {
            'mysql_engine': 'innodb',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci',
            'extend_existing': True,
            'mysql_row_format': 'dynamic'
        }
    )

    def __repr__(self):
        return f"<qos_host_date='{self.name}>"


    def object_to_dict(self, db: Session):
        result = {
            key: getattr(self, key)
            for key in self.__mapper__.c.keys()
        }
        return result


    # 删除一个日期所有的数据，重新入库
    @classmethod
    def delete_by_date(cls, db: Session, quality_date: str):
        db.query(cls).filter(
            cls.quality_date == quality_date
        ).delete()
        db.commit()



    # 账期内, 分析账单表, 根据field字段模糊查询value
    @classmethod
    def get_host_date_search(cls, db: Session, quality_date: str, page: int, per_page: int, field: str, value: str):
        try:
            search_field = []
            display_field = cls.get_display_field()
            if value or field != "all":
                for key, col in cls.__table__.c.items():
                    if field == "all" or key == field:
                        search_field.append(col.like(f"%{value}%"))

            result = db.query(cls).filter(
                cls.quality_date == quality_date,
                or_(*search_field)
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result


    # 日期内, 根据field字段聚合
    @classmethod
    def get_host_date_aggregate(cls, db: Session, quality_date: str, classes: str, field: str):
        try:
            search_field = [cls.quality_date == quality_date]
            if classes != "all":
                search_field.append(cls.classes == classes)

            display_field = [cls.quality_date]
            for key, col in cls.__table__.c.items():
                if key == field:
                    group_by_field = col
                    display_field.append(col)
                    break
            else:
                logger.error(f'get_host_date_aggregate, field:{field} not in table')
                return False, None

            data = db.query(cls).filter(
                *search_field
            ).with_entities(
                *display_field,
                func.count(cls.name).label('host_count')
            ).group_by(group_by_field).order_by("host_count").to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data



    # 日期内, 根据field字段排序
    @classmethod
    def get_host_date_sort(cls, db: Session, quality_date: str, classes: str, field: str):
        try:
            search_field = [cls.quality_date == quality_date]
            if classes != "all":
                search_field.append(cls.classes == classes)

            display_field = [
                cls.quality_date,
                cls.name,
                cls.classes,
                cls.department
            ]
            for key, col in cls.__table__.c.items():
                if key == field:
                    sort_field = col
                    display_field.append(col)
                    break
            else:
                logger.error(f'get_host_date_sort, field:{field} not in table')
                return False, None

            data = db.query(cls).filter(
                *search_field
            ).with_entities(
                *display_field
            ).order_by(sort_field.asc()).limit(10).to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data




    # 日期内, 根据field字段排序
    @classmethod
    def get_host_date_sort_livability(cls, db: Session, quality_date: str, classes: str):
        try:
            search_field = [
                cls.quality_date == quality_date,
                cls.livability != 100
            ]
            if classes != "all":
                search_field.append(cls.classes == classes)

            display_field = [
                cls.quality_date,
                cls.name,
                cls.classes,
                cls.department,
                cls.livability
            ]

            data = db.query(cls).filter(
                *search_field
            ).with_entities(
                *display_field
            ).order_by(cls.livability.asc()).limit(10).to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data